<?php
/**
 * Description: 费用导入导出控制器
 * User: wuhao
 * Date: 2017-06-01
 * Time: 15:18
 */
namespace app\backend\controller;

use app\backend\controller\traits\AuthManager;
use app\backend\model\BillManager;
use app\backend\model\Building;
use app\backend\model\Lease;
use app\backend\model\ExcelFile;
use app\backend\model\Member;
use app\backend\model\WaterPower;
use think\Controller;

class PropertyManagerController extends Controller
{
    use AuthManager;
    private $_memberId = null;
    private $_dtype    = null;
    private $_pdtype   = null;
    private $_pmid     = null;
    private $_allAuth  = [];
    public function __construct()
    {
        parent::__construct();
        $memberInfo = $this->getLoginInfo();
        $this->_memberId = $memberInfo['mid'];
        $this->_dtype    = $memberInfo['dtype'];
        $this->_pdtype   = $memberInfo['p_dtype'];
        $this->_pmid     = $memberInfo['p_mid'];
        $this->_allAuth  = $this->getPower($this->_memberId, $this->_dtype);
    }

    /**
     * 导出模板的格式设置
     * @date   2017-06-01
     * @author wuhao
     *
     * @return array
     */
    private function _exportSet()
    {
        //sheet目录设置
        $sheetTitle      = ['水费', '电费', '公摊水', '公摊电', '其他费用'];
        //标题设置
        $title = [
            'water_power' => [
                'A1' => '楼宇id(红色的为必填字段)', 'B1' => '楼栋', 'C1' => '房号', 'D1' => '开始周期(格式:YYYYMMDD)', 'E1' => '结束周期(格式:YYYYMMDD)', 'F1' => '上月度数', 'G1' => '本月度数', 'H1' => '实用度数', 'I1' => '金额（单位：元）'
            ],
            'gongtan' => [
                'A1' => '楼宇id(红色的为必填字段)', 'B1' => '楼栋', 'C1' => '房号', 'D1' => '开始周期(格式:YYYYMMDD)', 'E1' => '结束周期(格式:YYYYMMDD)', 'F1' => '金额（单位：元）'
            ],
            'other' => [
                'A1' => '楼宇id(红色的为必填字段)', 'B1' => '楼栋', 'C1' => '房号', 'D1' => '开始周期(格式:YYYYMMDD)', 'E1' => '结束周期(格式:YYYYMMDD)', 'F1' => '费用名称', 'G1' => '金额（单位：元）'
            ]
        ];

        //宽度设置
        $width = [
            'water_power' => [
                'A' => 15, 'B' => 15, 'C' => 15, 'D' => 25, 'E' => 25, 'F' => 20, 'G' => 20, 'H' => 20, 'I' => 25,
            ],
            'gongtan' => [
                'A' => 15, 'B' => 15, 'C' => 15, 'D' => 25, 'E' => 25, 'F' => 25,
            ],
            'other' => [
                'A' => 15, 'B' => 15, 'C' => 15, 'D' => 25, 'E' => 25, 'F' => 25, 'G' => 25,
            ]
        ];

        $data = [
            'sheet' => $sheetTitle,
            'title' => $title,
            'width' => $width,
        ];
        return $data;
    }

    /**
     * 导出模板
     * @date   2017-06-02
     * @author wuhao
     *
     * @param int  $buildingId  楼宇id
     */
    public function exportModel()
    {
        if (!in_array($this->_pdtype, [3, 4, 5, 6])) {
            $this->apiReturn(400, [], '无权操作');
        }

        //楼宇id, 判断楼宇的合法性
        $buildingId = I('building_id', '', 'intval');
        $buildModel = new Building();
        if ($this->_dtype == 7) {//员工账号
            $auth = $this->houseAuth($this->_memberId);
            $buildingIdArr = $buildModel->getHouseList(['id' => ['in', $auth]], 'building_id');
            $where = ['id' => ['in', array_column($buildingIdArr, 'building_id')]];
        } else {
            if ($this->_dtype == 5) {//物业
                $where = ['property_id' => $this->_pmid];
            } else {//水电
                $where = ['hydropower_id' => $this->_pmid];
            }
        }


        $res = $buildModel->getBuildingList($where, 'id, name');
        if (!$res) {
            $this->apiReturn(400, [], '没有可管理的楼宇');
        }
        $buildIdArr = array_column($res, 'id');
        if (!$buildingId  || !in_array($buildingId, $buildIdArr)) {
            $this->apiReturn(400, [], '所传楼宇信息有误');
        }

        //获取改楼宇对应的房源信息
        if ($this->_dtype != 7) {
            $roomInfo = $buildModel->getHouseList(['building_id' => $buildingId, 'is_delete' => 0], 'building_id, number, room');
        } else {
            $roomInfo = $buildModel->getHouseList(['id' => ['in', $auth], 'building_id' => $buildingId, 'is_delete' => 0], 'building_id, number, room');
        }

        if (!$roomInfo) {
            $this->apiReturn(400, [], '找不到对应的房源信息');
        }

        //设置导出样式等
        $exportSet  = $this->_exportSet();
        $sheetTitle = $exportSet['sheet'];
        $title      = $exportSet['title'];
        $width      = $exportSet['width'];
        $objPHPExcel     = load_excel();
        $objWriter       = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $countTotal      = count($sheetTitle);

        //循环生成模板
        for($i = 0; $i< $countTotal; $i++) {
            if ($i > 0) {
                $objPHPExcel->createSheet();
            }
            $objPHPExcel->setActiveSheetIndex($i);//设置工作sheet
            $objPHPExcel->getActiveSheet()->setTitle($sheetTitle[$i]); //设置工作表名称

            //设置对应的宽度，费用标题栏
            if ($i == 0 || $i == 1) {//水电费
                $key = 'water_power';
                $objPHPExcel->getActiveSheet()->getStyle( 'A1:E1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
               // $objPHPExcel->getActiveSheet()->getStyle( 'I1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
            } elseif ($i == 2 || $i == 3) {//公摊水电
                $key = 'gongtan';
                $objPHPExcel->getActiveSheet()->getStyle( 'A1:E1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
            } else {//其他
                $key = 'other';
                $objPHPExcel->getActiveSheet()->getStyle( 'A1:E1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
            }
            foreach ($width[$key] as $a => $b) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($a)->setWidth($b);
            }
            foreach ($title[$key] as $c => $d) {
                $objPHPExcel->getActiveSheet()->setCellValue($c, $d);
            }
            unset($key);

            //导入具体数据
            $num=1;
            foreach ($roomInfo as $e => $f) {
                ++$num;
                $objPHPExcel->getActiveSheet()->setCellValue('A'.$num, $f['building_id']);
                $objPHPExcel->getActiveSheet()->setCellValue('B'.$num, $f['number']);
                $objPHPExcel->getActiveSheet()->setCellValue('C'.$num, $f['room']);
            }
        }
        $fileName = '物业水电管理'.date('Ymd').'.xls';
        ob_end_clean();
        header('Pragma: public');
        header('Expires: 0');
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Content-Type:application/force-download');
        header('Content-Type:application/vnd.ms-execl');
        header('Content-Type:application/octet-stream');
        header('Content-Type:application/download');
        header("Content-Disposition:attachment;filename={$fileName}");
        header('Content-Transfer-Encoding:binary');
        $objWriter->save('php://output');
    }

    /**
     * 读取并验证excel数据
     * @date   2017-06-02
     * @author wuhao
     *
     * @param string  $file  文件地址
     * @param string  $filename 文件名
     */
    public function readExcel()
    {
        if (!in_array($this->_pdtype, [3, 4, 5, 6])) {
            $this->apiReturn(400, [], '无权操作');
        }
        $file = I('file', '', 'strval');
        $filename = I('filename', '', 'strval');
        //$file = "http://zft.12301.local/excel/property_manager/access/60/14967147734451.xls";
        //$filename = '物业水电管理.xls';
        if (!$file) {
            $this->apiReturn(400, [], '文件地址有误');
        }

        //获取这个文件的地址,将主域名替换成目录名
        $fileInfo = str_replace(MAIN_DOMAIN, EXCEL_REMOVE_DIR, $file);
        $excel = pathinfo($fileInfo);
        //dd($excel);
        // 文件类型校验
        if (!in_array($excel['extension'], ['xls', 'xlsx', 'csv'])) {
            $this->apiReturn(400, [], '不是excel文件');
        }

        $e = file_get_contents($fileInfo);
        if (!$e) {
            $this->apiReturn(400, [], '文件获取失败');
        }

        $res = $this->_filterData($fileInfo);

        if (!$res[0] && !$res[1] && !$res[2] && !$res[3] && !$res[4]) {
            unlink($fileInfo);//删除这个文件
            $this->apiReturn(400, [], '所传的文件数据为空，请重新上传');
        }
        $countTotal = 0;
        foreach ($res as $v) {
            $countTotal += count($v);
        }
        $fileModel = new ExcelFile();
        $fileId = $fileModel->addFile($filename, $this->_pmid, $file, date('Y-m-d H:i:s'), $countTotal, 2);
        if (!$fileId) {
            zft_log('property_manager', $fileModel->getLastSql());
            $this->apiReturn(400, [], '记录文件失败');
        }
        //验证数据3秒后后台跑
        sleep(3);
        set_time_limit(0);
        $resData = $this->_checkData($res);
        $accessData = $resData['access'];
        $wrongData  = $resData['wrong'];
        $countAccess = count($accessData[0])+count($accessData[1]) + count($accessData[2]) +count($accessData[3]) +count($accessData[4]);
        $countWrong  = count($wrongData[0]) + count($wrongData[1]) + count($wrongData[2]) + count($wrongData[3]) + count($wrongData[4]);
        $total =  $countWrong+$countAccess;


        if (!$wrongData[0] && !$wrongData[1] && !$wrongData[2] && !$wrongData[3] && !$wrongData[4]) {
            //没有错误的数据
            $updateData = [
                'record'          => $total,
                'access_record'   => $countAccess,
                'validate_status' => 1,
                'status'          => 1,
            ];
            $res = $fileModel->updateFile($fileId, $updateData);
            if (!$res) {
                zft_log('property_manager', $fileModel->getLastSql());
            }
            echo 1;
            exit();
        } else {
            //保存错误数据到模板
            $wrongUrl = $this->_saveWrongModel($accessData, $wrongData);
            $updateData = [
                'record'          => $total,
                'access_record'   => $countAccess,
                'validate_status' => 1,
                'status'          => 0,
                'wrong_url'       => $wrongUrl
            ];
            $res = $fileModel->updateFile($fileId, $updateData);
            if (!$res) {
                zft_log('property_manager', $fileModel->getLastSql());
            }
            echo 2;
            exit();
        }
    }

    /**
     * 读取excel数据,过滤掉为空的数据
     * @date   2017-06-06
     * @author wuhao
     *
     * @param string  $fileInfo  文件的绝对地址
     * @return array $res[0]水  $res[1]电  $res[2]公摊水  $res[3]公摊电 $res[4]其他
     */
    private function _filterData($fileInfo)
    {
        $objPHPExcel = load_excel();
        $reader = \PHPExcel_IOFactory::createReader('Excel5');
        $phpExcel = $reader->load($fileInfo,$encode='utf-8');

        //设置导出样式等
        $exportSet  = $this->_exportSet();
        $sheetTitle = $exportSet['sheet'];
        $countTotal = count($sheetTitle);

        //读取excel数据
        $data = [];
        for ($i = 0; $i<$countTotal; $i++) {
            $sheet = $phpExcel->getSheet($i);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
            $highestColumm = $sheet->getHighestColumn(); // 取得总列数

            $tmpData = [];
            for ($row = 2; $row <= $highestRow; $row++) {//内容是以第2行开始
                for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始
                    $tmp = $sheet->getCell($column.$row)->getValue(); //读取到数组中
                    if (is_object($tmp)) {
                        $tmp = $tmp->__toString();
                    }

                    $tmpData[$row][] = trim($tmp); //读取到数组中
                    unset($tmp);
//                echo $column.$row.":".$sheet->getCell($column.$row)->getValue()."<br />";
                }
            }
            $tmpData = array_merge($tmpData);
            $data[] = $tmpData;
            unset($dataset, $highestRow, $highestColumm);
        }

        //过滤为空的数据
        $res = [];
        foreach ($data as $k => $v) {
            $res[$k] = [];
            foreach ($v as $a => $b) {
                if ($b && $b[0] && $b[1] && $b[2] && $b[3] && $b[4]) {//楼宇id，楼栋，房号，周期有设置
                    $res[$k][] = $v[$a];
                }
            }
        }
        return $res;
    }

    /**
     * 读取并验证excel数据
     * @date   2017-06-05
     * @author wuhao
     *
     * @param array  $data  要验证的数据
     * @return array
     */
    private function _checkData($data)
    {
        $waterFee = $data[0];
        $powerFee = $data[1];
        $gtWater  = $data[2];
        $gtPower  = $data[3];
        $other    = $data[4];
        $billModel = new BillManager();
        $buildModel = new Building();
        $leaseModel  = new Lease();
        $data[4] = $data[3] = $data[2] = $data[1] = $data[0] =  [];
        $wrongData[4] = $wrongData[3] = $wrongData[2] = $wrongData[1] = $wrongData[0] =  [];
        if ($waterFee) {
            $waterData    = $this->_checkDetailData($waterFee, $billModel, $leaseModel, $buildModel, 0);
            $data[0]      = $waterData['access'];
            $wrongData[0] = $waterData['wrong'];
        }

        if ($powerFee) {
            $powerData    = $this->_checkDetailData($powerFee, $billModel, $leaseModel, $buildModel, 1);
            $data[1]      = $powerData['access'];
            $wrongData[1] = $powerData['wrong'];
        }

        if ($gtWater) {
            $gtWaterData  = $this->_checkDetailData($gtWater, $billModel, $leaseModel, $buildModel, 2);
            $data[2]      = $gtWaterData['access'];
            $wrongData[2] = $gtWaterData['wrong'];
        }

        if ($gtPower) {
            $gtPowerData  = $this->_checkDetailData($gtPower, $billModel, $leaseModel, $buildModel, 3);
            $data[3]      = $gtPowerData['access'];
            $wrongData[3] = $gtPowerData['wrong'];
        }

        if ($other) {
            $otherData    = $this->_checkDetailData($other, $billModel, $leaseModel, $buildModel, 4);
            $data[4]      = $otherData['access'];
            $wrongData[4] = $otherData['wrong'];
        }
        $res = [
            'access' => $data,
            'wrong'  => $wrongData,
        ];
        return $res;
    }

    /**
     * 验证详细的数据
     * @date   2017-06-05
     * @author wuhao
     *
     * @param array       $data         要验证的数据
     * @param BillManager $billModel    账单查询模型
     * @param Lease       $leaseModel   合同查询模型
     * @param Building    $buildingModel 房源查询模型
     * @param int         $type          费用类型
     * @return array
     */
    private function _checkDetailData($data, BillManager $billModel, Lease $leaseModel, Building $buildingModel, $type = 0) {
        $accessData = $wrongData = [];
        foreach ($data as $a => $b) {
            //先判断参数格式对不对
            if (!is_numeric($b[0]) || $b[0] <=0) {
                $b['comment'] = '楼宇id有误';
                $wrongData[] = $b;
            } elseif (!$b[1] || !$b[2] ) {
                $b['comment'] = '房源信息有误';
                $wrongData[] = $b;
            } elseif (!is_numeric($b[3]) || !is_numeric($b[4]) || !strtotime($b[3]) || !strtotime($b[4])) {
                $b['comment'] = '周期有误';
                $wrongData[] = $b;
            } else {
                if ($type == 0 || $type==1) {//水电
                    if ($b[5] && (!is_numeric($b[5]) || $b[5] <0)) {
                        $b['comment'] = '度数有误';
                        $wrongData[] = $b;
                        continue;
                    }
                    if ($b[6] && (!is_numeric($b[6]) || $b[6] <0)) {
                        $b['comment'] = '度数有误';
                        $wrongData[] = $b;
                        continue;
                    }
                    if ($b[7] && (!is_numeric($b[7]) || $b[7] <0)) {
                        $b['comment'] = '度数有误';
                        $wrongData[] = $b;
                        continue;
                    }

                    if ($b[8] && (!is_numeric($b[8]) || $b[8]<0)) {
                        $b['comment'] = '金额有误';
                        $wrongData[] = $b;
                        continue;
                    }
                } elseif ($type == 2 || $type == 3) {//公摊水电
                    if ($b[5] && (!is_numeric($b[5]) || $b[5]<0)) {
                        $b['comment'] = '金额有误';
                        $wrongData[] = $b;
                        continue;
                    }
                } else {//其他
                    if ($b[6] && (!is_numeric($b[6]) || $b[6]<0)) {
                        $b['comment'] = '金额有误';
                        $wrongData[] = $b;
                        continue;
                    }
                }

                //再去验证其他信息
                $houseInfo = $buildingModel->getHouseInf($b[0], $b[1], $b[2], 'building_id,number,room,house_number');
                if (!$houseInfo) {//找不到房源数据
                    $b['comment'] = '找不到房源数据';
                    $wrongData[] = $b;
                } else { //查找对应的合同数据
                    $leaseInfo = $leaseModel->getLeaseExtByHouse($houseInfo['house_number'], 'ordernum, house_number');
                    if (!$leaseInfo) {//找不到合同数据
                        $b['comment'] = '找不到合同数据';
                        $wrongData[] = $b;
                    } else {//查找对应的账单信息
                        //, 'begin_cycle' => $b[3], 'end_cycle'=>$b[4]
                        $accessData[] = $b;
//                        $billTime = date('Ym', strtotime($b[3]));
//                        $billInfo = $billModel->getPayBillList(['ordernum' => $leaseInfo['ordernum'], 'bill_time' => $billTime],
//                            'ordernum,tenant_id,charge_out,type,begin_cycle, end_cycle, bill_time');
//                        if ($billInfo && ($billInfo[0]['begin_cycle'] != $b[3]  || $billInfo[0]['end_cycle'] != $b[4])) {//当月账单周期未对上
//                            $b['comment'] = '当月账单周期不一致, room'.$b[2];
//                            $wrongData[] = $b;
//                        } else {
//                            //$accessData[] = $b;
//                        }
                    }
                }
            }
        }
        $data = [
            'access' => $accessData,
            'wrong'  => $wrongData,
        ];
        return $data;
    }

    /**
     * 导出存在错误的数据模板
     * @date   2017-06-05
     * @author wuhao
     *
     * @param array       $accessData   正确的数据
     * @param array       $wrongData    错误的数据
     * @return string 文件地址
     */
    private function _saveWrongModel($accessData, $wrongData)
    {
        //设置导出样式等
        $exportSet  = $this->_exportSet();
        $sheetTitle = $exportSet['sheet'];
        $title      = $exportSet['title'];
        $width      = $exportSet['width'];
        $objPHPExcel     = load_excel();
        $objWriter       = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $countTotal      = count($sheetTitle);

        //循环生成模板
        for($i = 0; $i< $countTotal; $i++) {
            if ($i > 0) {
                $objPHPExcel->createSheet();
            }
            $objPHPExcel->setActiveSheetIndex($i);//设置工作sheet
            $objPHPExcel->getActiveSheet()->setTitle($sheetTitle[$i]); //设置工作表名称

            //设置对应的宽度，费用标题栏
            if ($i == 0 || $i == 1) {//水电费
                $key = 'water_power';
            } elseif ($i == 2 || $i == 3) {//公摊水电
                $key = 'gongtan';
            } else {//其他
                $key = 'other';
            }
            foreach ($width[$key] as $a => $b) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($a)->setWidth($b);
            }
            foreach ($title[$key] as $c => $d) {
                $objPHPExcel->getActiveSheet()->setCellValue($c, $d);
            }
            unset($key);

            //导入具体数据
            $num=1;
            foreach ($accessData[$i] as $a => $b) {
                if ($b) {
                    ++$num;
                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$num, $b[0]);
                    $objPHPExcel->getActiveSheet()->setCellValue('B'.$num, $b[1]);
                    $objPHPExcel->getActiveSheet()->setCellValue('C'.$num, $b[2]);
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.$num, $b[3]);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.$num, $b[4]);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.$num, $b[5]);
                    if ($i == 0 || $i == 1) {//水电
                        $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $b[6]);
                        $objPHPExcel->getActiveSheet()->setCellValue('H'.$num, $b[7]);
                        $objPHPExcel->getActiveSheet()->setCellValue('I'.$num, $b[8]);
                    } elseif ($i == 4) {//其他
                        $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $b[6]);
                    }
                }
            }
            foreach ($wrongData[$i] as $c => $d) {
                if ($d) {
                    ++$num;
                    //错误的记录设置背景色
                    $objPHPExcel->getActiveSheet()->getStyle('A'.$num.':'.'k'.$num)->getFill()
                        ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('FFFF00');

                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$num, $d[0]);
                    $objPHPExcel->getActiveSheet()->setCellValue('B'.$num, $d[1]);
                    $objPHPExcel->getActiveSheet()->setCellValue('C'.$num, $d[2]);
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.$num, $d[3]);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.$num, $d[4]);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.$num, $d[5]);
                    if ($i == 0 || $i == 1) {//水电
                        $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $d[6]);
                        $objPHPExcel->getActiveSheet()->setCellValue('H'.$num, $d[7]);
                        $objPHPExcel->getActiveSheet()->setCellValue('I'.$num, $d[8]);
                        $objPHPExcel->getActiveSheet()->setCellValue('J'.$num, $d['comment']);
                    } elseif ($i == 4) {//其他
                        $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $d[6]);
                        $objPHPExcel->getActiveSheet()->setCellValue('H'.$num, $d['comment']);
                    } else {
                        $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $d['comment']);
                    }
                }
            }
        }

        $fileName = '物业水电管理错误导出'.date('Ymd_His').'.xls';
        $year = date('Y');
        $month = date('m');
        $day = date('d');
        $time = $year.'/'.$month.'/'.$day;
        $pathStr = EXCEL_IMPORT_DIR.'property_manager/wrong/' . $time;
        $downloadStr = MAIN_DOMAIN.'excel/property_manager/wrong/' . $time;
        createDir($pathStr);
/*        header('Pragma: public');
        header('Expires: 0');
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Content-Type:application/force-download');
        header('Content-Type:application/vnd.ms-execl');
        header('Content-Type:application/octet-stream');
        header('Content-Type:application/download');
        header("Content-Disposition:attachment;filename={$fileName}");
        header('Content-Transfer-Encoding:binary');*/
        $objWriter->save($pathStr.'/'.$fileName);
        return $downloadStr.'/'.$fileName;
    }

    /**
     * 生成账单或审核列表
     * @date   2017-06-06
     * @author wuhao
     *
     * @param int   $fileId  上传文件的id
     * @param int   $type    生成账单=1  等待审批=2
     * @return json
     */
    public function generate()
    {
        if (!in_array($this->_pdtype, [3, 4, 5, 6])) {
            $this->apiReturn(400, [], '无权操作');
        }
        $fileId = I('file_id', '', 'intval');
        //$fileId=2;
        $type   = I('type', 1, 'intval');
        //$type=2;
        if (!$fileId || !in_array($type, [1,2])) {
            $this->apiReturn(400, [], '所传参数有误');
        }

        //查找基本信息
        $excelFileModel = new ExcelFile();
        $fileInf = $excelFileModel->getFile($fileId);
        if (!$fileInf) {
            $this->apiReturn(400, [], '文件不存在');
        }
        if ($fileInf['validate_status'] == 0) {
            $this->apiReturn(400, [], '正在验证中，稍后重试');
        }
        if ($fileInf['handle_status'] != 0) {
            $this->apiReturn(400, [], '正在审核中');
        }

        $file = $fileInf['url'];
        //获取这个文件的地址,将主域名替换成目录名
        $fileInfo = str_replace(MAIN_DOMAIN, EXCEL_REMOVE_DIR, $file);
        $excel = pathinfo($fileInfo);
        //dd($excel);
        // 文件类型校验
        if (!in_array($excel['extension'], ['xls', 'xlsx', 'csv'])) {
            $this->apiReturn(400, [], '不是excel文件');
        }
        if (!file_exists($fileInfo)) {
            $this->apiReturn(400, [], '文件不存在');
        }

        $e = file_get_contents($fileInfo);
        if (!$e) {
            $this->apiReturn(400, [], '文件获取失败');
        }

        $res = $this->_filterData($fileInfo);
        if (!$res[0] && !$res[1] && !$res[2] && !$res[3] && !$res[4]) {
            unlink($fileInfo);//删除这个文件
            $this->apiReturn(400, [], '所传的文件数据为空，请重新上传');
        }

        //判断是否已经生成对应的了
        $check = $excelFileModel->getPropByFileId($fileId);
        if ($check) {
            $this->apiReturn(400, [], '已插入到审批列表，请勿重复操作');
        }


        $handleStatus = $excelFileModel->updateFile($fileId, ['handle_status' => 1]);//更新为正在处理中的状态
        if (!$handleStatus) {
            zft_log('property_manager', $excelFileModel->getLastSql());
            $this->apiReturn(400, [], '审核状态更新失败');
        }

        sleep(3);
        set_time_limit(0);//无限制时间
        $billModel = new BillManager();
        $buildModel = new Building();
        $leaseModel  = new Lease();

        $data = [];

        foreach ($res as $k => $v) {
            if ($v) {
                $tmp   = $this->_getBillInfo($v, $this->_pmid, $fileId,  $billModel, $leaseModel, $buildModel, $k);
                if ($tmp['code'] != 200) {
                    $this->apiReturn($tmp['code'] , [], $tmp['msg'] );
                }
                $data[] = $tmp['data'];
            }
        }

        $resData = [];
        foreach ($data as $a => $b) {
            foreach ($b as $c => $d) {
                if ($d) {
                    $resData[] = $d;
                }
            }
        }

        //var_dump($resData);die;
        //先生成审批列表
        $res1 = $excelFileModel->insertPropFee($resData);

        if (!$res1) {
            zft_log('excel_file', $excelFileModel->getLastSql());
            echo -1;exit();
        }
        if ($type == 2) {
            $handleStatus = $excelFileModel->updateFile($fileId, ['handle_status' => 3]);//更新为人工审核
            if (!$handleStatus) {
                zft_log('property_manager', $excelFileModel->getLastSql());
                echo -2;exit();
            }
            echo 1;exit();
        } else {//直接生成账单
            //获取审批列表
            $detailList = $excelFileModel->getPropByFileId($fileId,
                'id, ordernum, mid, tenant_id, building_id,number, room, begin_cycle, end_cycle, fee_name, fee, fee_type, charge_out, charge_type, bill_time,
                last_water, this_water, actual_water, last_power, this_power, actual_power');
            $feeData = [];
            $waterPower = [];
            foreach ($detailList as $e => $f) {
                $feeData[] = [
                    'ordernum'    => $f['ordernum'],
                    'building_id' => $f['building_id'],
                    'charge_out'  => $f['charge_out'],
                    'type'        => $f['charge_type'],
                    'begin_cycle' => $f['begin_cycle'],
                    'end_cycle'   => $f['end_cycle'],
                    'owner_id'    => $f['mid'],
                    'tenant_id'   => $f['tenant_id'],
                    'money'       => $f['fee'],
                    'time'        => time(),
                    'bill_time'   => $f['bill_time'],
                    'status'      => 0,
                    'bill_name'   => $f['fee_name'],
                    'type_money'  => $f['fee_type'],
                ];
                if (in_array($f['fee_type'], [3, 4])) {
                    $waterPower[] = [
                        'ordernum'    => $f['ordernum'],
                        'begin_cycle' => $f['begin_cycle'],
                        'end_cycle'   => $f['end_cycle'],
                        'building_id' => $f['building_id'],
                        'number'      => $f['number'],
                        'room'        => $f['room'],
                        'last'        => $f['fee_type'] == 3 ? $f['last_water'] : $f['last_power'],
                        'this'        => $f['fee_type'] == 3 ? $f['this_water'] : $f['this_power'],
                        'actual'      => $f['fee_type'] == 3 ? $f['actual_water'] : $f['actual_power'],
                        'fee_type'    => $f['fee_type']
                    ];
                }
            }
            //插入到账单
            $res2 = $billModel->insertPayBill($feeData);
            if (!$res2) {
                zft_log('property_manager', $billModel->getLastSql());
                echo -3;exit();
            }

            //插入到水电度数表
            if ($waterPower) {
                $waterPowerModel = new WaterPower();
                $res3 = $waterPowerModel->addList($waterPower);
                if (!$res3) {
                    zft_log('property_manager', $waterPowerModel->getLastSql());
                    $this->apiReturn(203, [], '添加水电安读书失败');
                }
            }

            //更新审核列表状态
            $res4 = $excelFileModel->updateProp(['file_id' => $fileId], ['status'=>3, 'verify_time'=> date('Y-m-d H:i:s')]);
            if (!$res4) {
                zft_log('property_manager', $billModel->getLastSql());
                echo -1;exit();
            }

            //更新为审核的状态,并更新处理个数
            $handleStatus = $excelFileModel->updateFile($fileId, ['handle_status' => 2]);
            if (!$handleStatus) {
                zft_log('property_manager', $excelFileModel->getLastSql());
                echo -4;exit();
            }
            echo 1;exit();
        }
    }

    /**
     * 获取对应的详细的数据
     * @date   2017-06-05
     * @author wuhao
     *
     * @param array       $data         要验证的数据
     * @param int         $mid          创建人id
     * @param int         $fileId       对应文件的id
     * @param BillManager $billModel    账单查询模型
     * @param Lease       $leaseModel   合同查询模型
     * @param Building    $buildingModel 房源查询模型
     * @param int         $type          数据的类型 0=水 1=电 2=公摊水 3=公摊电 4=其他
     * @return array
     */
    private function _getBillInfo($data, $mid, $fileId, BillManager $billModel, Lease $leaseModel, Building $buildingModel, $type = 0)
    {
        $res = [];
        foreach ($data as $a => $b) {
            //先过滤为空的数据
            if ($type == 0) {//水
                if (!$b[5] && !$b[6] && !$b[7] && !$b[8]) {
                    continue;
                }
            } elseif ($type == 1) {//电
                if (!$b[5] && !$b[6] && !$b[7] && !$b[8]) {
                    continue;
                }
            } elseif ($type == 2 || $type == 3) {//公摊水，公摊电
                if (!$b[5]) {
                    continue;
                }
            } else {//其他
                if (!$b[5] || !$b[6]) {
                    continue;
                }
            }

            $houseInfo = $buildingModel->getHouseInf($b[0], $b[1], $b[2], 'building_id,number,room,house_number');
            if (!$houseInfo) {//找不到房源数据
                return ['code' => 201, 'msg' => '找不到房源数据, room'.$b[2]];
            } else { //查找对应的合同数据
                $leaseInfo = $leaseModel->getLeaseExtByHouse($houseInfo['house_number'], 'ordernum, house_number');
                if (!$leaseInfo) {//找不到合同数据
                    return ['code' => 201, 'msg' => '找不到合同数据, room'.$b[2]];
                } else {//查找对应的账单信息
                    //, 'begin_cycle' => $b[3], 'end_cycle' => $b[4]
                    $billTime = date('Ym', strtotime($b[3]));
                    $tenantId = $billModel->getPayBillTenant($leaseInfo['ordernum']);
                    $tenantId = $tenantId['tenant_id'];
//                    $billInfo = $billModel->getPayBillList(['ordernum' => $leaseInfo['ordernum'], 'bill_time' => $billTime],
//                        'ordernum,tenant_id,charge_out,type,begin_cycle, end_cycle, bill_time');
//                    if ($billInfo && ($billInfo[0]['begin_cycle'] != $b[3]  || $billInfo[0]['end_cycle'] != $b[4])) {//当月账单周期未对上
//                        return ['code' => 201, 'msg' => '当月账单周期不一致, room'.$b[2]];
//                    } else {
                    if ($type == 0) {//水
                        $lastPower   = $thisPower = $actualPower = $feeName = '';
                        $lastWater   = isset($b[5]) ? fen($b[5]) : 0;
                        $thisWater   = isset($b[6]) ? fen($b[6]) : 0;
                        $actualWater = isset($b[7]) ? fen($b[7]) : 0;
                        $fee         = isset($b[8]) ? fen($b[8]) : 0;
                        $feeType     = 3;
                    } elseif ($type == 1) {//电
                        $lastWater   = $thisWater = $actualWater = $feeName = '';
                        $lastWater   = isset($b[5]) ? fen($b[5]) : 0;
                        $thisWater   = isset($b[6]) ? fen($b[6]) : 0;
                        $actualWater = isset($b[7]) ? fen($b[7]) : 0;
                        $fee         = isset($b[8]) ? fen($b[8]) : 0;
                        $feeType     = 4;
                    } elseif ($type == 2 || $type == 3) {//公摊水，公摊电
                        $actualPower = $thisPower = $lastPower = $lastWater = $thisWater = $actualWater = $feeName = '';
                        $fee         = isset($b[5]) ? fen($b[5]) : 0;
                        $feeType     = $type == 2 ? 5 : 6;
                    } else {//其他
                        $actualPower = $thisPower = $lastPower = $lastWater = $thisWater = $actualWater = '';
                        $feeName     = $b[5];
                        $fee         =  isset($b[6]) ? fen($b[6]) : 0;
                        $feeType     = 8;
                    }
                    $tmp = [
                        'file_id'      => $fileId,
                        'file_type'    => 0,//文件类型 0=物业水电管理
                        'mid'          => $mid,
                        'tenant_id'    => $tenantId,
                        'building_id'  => isset($b[0]) ? $b[0] : 0,
                        'number'       => $b[1] ?: '',
                        'room'         => $b[2]?: '',
                        'begin_cycle'  => $b[3]?: '',
                        'end_cycle'    => $b[4]?: '',
                        'house_number' => $houseInfo['house_number'],
                        'ordernum'     => $leaseInfo['ordernum'],
                        'last_water'   => is_null($lastWater) ? '' : $lastWater,
                        'this_water'   => is_null($thisWater) ? '' : $thisWater,
                        'actual_water' => is_null($actualWater) ? '' : $actualWater,
                        'last_power'   => is_null($lastPower) ? '' : $lastPower,
                        'this_power'   => is_null($thisPower) ? '' : $thisPower,
                        'actual_power' => is_null($actualPower) ? '' : $actualPower,
                        'fee_name'     => is_null($feeName) ? '' : $feeName,
                        'fee'          => is_null($fee) ? '' : $fee,
                        'fee_type'     => is_null($feeType) ? '' : $feeType,
                        'charge_out'   => isset($billInfo[0]['charge_out']) ? $billInfo[0]['charge_out'] : date('Ymd'),
                        'charge_type'  => isset($billInfo[0]['type']) ? $billInfo[0]['type'] : 1,
                        'bill_time'    => isset($billInfo[0]['bill_time']) ? $billInfo[0]['bill_time'] : $billTime,
                        'status'       => 0,//还未审核
                        'verify_mid'   => 0,//审核人id
                        'create_time'  => date('Y-m-d H:i:s')
                    ];
                    $res[] = $tmp;
                    unset($tmp);
                    //}
                }
            }
        }
        return ['code'=>200, 'data'=>$res];
    }

    /**
     * 获取费用审批列表
     * @date   2017-06-08
     * @author wuhao
     *
     * @param int       $buildId       楼宇id
     * @param int       $feeType       费用类型 -1全部 3水，4电, 5公摊水，6公摊电,  8其他费用
     * @param int       $page          页数
     * @param int       $pageSize      每页个数
     * @return json
     */
    public function getProp()
    {
        if (!in_array($this->_pdtype, [3, 4])) {
            $this->apiReturn(400, [], '无权操作');
        }

        $buildId  = I('post.building_id', '-1', 'intval');
        $feeType  = I('post.fee_type', '-1', 'intval');
        $page     = I('post.page', 1, 'intval');
        $pageSize = I('post.page_size', 15, 'intval');
        if (!$buildId || !in_array($feeType, [-1,3,4,5,6,8]) || !$page || !$pageSize) {
            $this->apiReturn(400, [], '参数有误');
        }
        $fileModel = new ExcelFile();
        $res = $fileModel->getProp($buildId, $this->_pmid, $feeType, $page, $pageSize);
        if ($res) {
            $buildIdArr = array_column($res['list'], 'building_id');
            //获取楼宇名称
            $buildModel = new Building();
            $buildInfo = $buildModel->getBuildingList(['id' => ['in', $buildIdArr], 'is_delete' => 0], 'id,name');
            //获取导入人名称
            $midArr = array_column($res['list'], 'mid');
            $memberModel = new Member();
            $memberInfo = $memberModel->getMemberInfo(['id' => ['in', $midArr]], 'id,nickname');
            foreach ($res['list'] as $k => $v) {
                $res['list'][$k]['fee'] = yuan($v['fee']);
                $res['list'][$k]['last_water'] = yuan($v['last_water']);
                $res['list'][$k]['this_water'] = yuan($v['this_water']);
                $res['list'][$k]['actual_water'] = yuan($v['actual_water']);
                $res['list'][$k]['last_power'] = yuan($v['last_power']);
                $res['list'][$k]['this_water'] = yuan($v['this_water']);
                $res['list'][$k]['actual_power'] = yuan($v['actual_power']);
                foreach ($buildInfo as $b) {
                    if ($b['id'] == $v['building_id']) {
                        $res['list'][$k]['build_name'] = $b['name'];
                        break;
                    }
                }
                foreach ($memberInfo as $m) {
                    if ($m['id'] == $v['mid']) {
                        $res['list'][$k]['user_name'] = $m['nickname'];
                        break;
                    }
                }
            }
            $this->apiReturn(200, $res, '');
        } else {
            $this->apiReturn(201, [], '没有数据');
        }
    }

    /**
     * 获取费用审批列表
     * @date   2017-06-08
     * @author wuhao
     *
     * @param array     $idArr      审批id数组
     * @param int       $status     通过=1 拒绝=2
     * @return json
     */
    public function verifyProp()
    {
        if (!in_array($this->_pdtype, [3, 4])) {
            $this->apiReturn(400, [], '无权操作');
        }
        $idArr  = I('post.id_arr');
        $status = I('post.status', 1, 'intval');
        $idArr  = json_decode($idArr, true);
        //$idArr = [589,590];$status=1;
        if (!$idArr || !is_array($idArr) || !in_array($status, [1,2])) {
            $this->apiReturn(400, [], '参数有误');
        }
        $fileModel = new ExcelFile();
        //获取未审批的
        $propList = $fileModel->getPropById($idArr, 0,
            'id, ordernum, mid, tenant_id, file_id, building_id, number, room, begin_cycle, end_cycle, fee_name, fee, fee_type, charge_out, charge_type, bill_time,
            last_water, this_water, actual_water, last_power, this_power, actual_power');
        if (!$propList) {
            $this->apiReturn(400, [], '无数据');
        }
        //审批的id列表
        $propIdArr = array_column($propList, 'id');

        if ($status == 2) {//拒绝
            $res = $fileModel->updateProp(['id' => ['in', $propIdArr]],
                ['status' => 2, 'verify_time' => date('Y-m-d H:i:s'), 'verify_mid' => $this->_memberId]);
            if (!$res) {
                zft_log('property_manager', $fileModel->getLastSql());
                $this->apiReturn(201, [], '更新状态出错');
            } else {

                $this->apiReturn(200, [], '处理成功');
            }

        } else {//通过
            $res = $fileModel->updateProp(['id' => ['in', $propIdArr]],
                ['status' => 1, 'verify_time' => date('Y-m-d H:i:s'), 'verify_mid' => $this->_memberId]);
            if (!$res) {
                zft_log('property_manager', $fileModel->getLastSql());
                $this->apiReturn(201, [], '更新状态出错');
            } else {
                $feeData = [];
                //水电账单对应的信息
                $waterPower = [];
                foreach ($propList as $e => $f) {
                    $feeData[] = [
                        'ordernum'    => $f['ordernum'],
                        'building_id' => $f['building_id'],
                        'charge_out'  => $f['charge_out'],
                        'type'        => $f['charge_type'],
                        'begin_cycle' => $f['begin_cycle'],
                        'end_cycle'   => $f['end_cycle'],
                        'owner_id'    => $f['mid'],
                        'tenant_id'   => $f['tenant_id'],
                        'money'       => $f['fee'],
                        'time'        => time(),
                        'bill_time'   => $f['bill_time'],
                        'status'      => 0,
                        'bill_name'   => $f['fee_name'],
                        'type_money'  => $f['fee_type'],
                    ];
                    if (in_array($f['fee_type'], [3, 4])) {
                        $waterPower[] = [
                            'ordernum'    => $f['ordernum'],
                            'begin_cycle' => $f['begin_cycle'],
                            'end_cycle'   => $f['end_cycle'],
                            'building_id' => $f['building_id'],
                            'number'      => $f['number'],
                            'room'        => $f['room'],
                            'last'        => $f['fee_type'] == 3 ? $f['last_water'] : $f['last_power'],
                            'this'        => $f['fee_type'] == 3 ? $f['this_water'] : $f['this_power'],
                            'actual'      => $f['fee_type'] == 3 ? $f['actual_water'] : $f['actual_power'],
                            'fee_type'    => $f['fee_type']
                        ];
                    }
                }

                //插入到账单
                $billModel = new BillManager();
                $res2 = $billModel->insertPayBill($feeData);
                if (!$res2) {
                    zft_log('property_manager', $billModel->getLastSql());
                    $this->apiReturn(202, [], '添加账单失败');
                }

                //插入到水电度数表
                if ($waterPower) {
                    $waterPowerModel = new WaterPower();
                    $res3 = $waterPowerModel->addList($waterPower);
                    if (!$res3) {
                        zft_log('property_manager', $waterPowerModel->getLastSql());
                        $this->apiReturn(203, [], '添加水电安读书失败');
                    }
                }

                $this->apiReturn(200, [], '处理成功');
            }
        }
    }
}